using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Drawing;
using System.Reflection;
using System.Globalization;
using System.Threading;
using System.Text.RegularExpressions;
using System.Collections.Generic;
namespace Repo {
	public partial class Misc:System.Web.UI.Page {

		//SQL dal=new SQL("server=pon-wdsq10\\IN2012;uid=webuser;pwd=c0mputer");
		SQL dal=new SQL("server=(local);integrated security=sspi");
		protected void Page_Load(object sender,EventArgs e) {
			string usr=Page.User.Identity.Name.Split('\\')[1];
			form1.Controls.Add(new Elem("<!--{0}-->",usr));
			//if(usr.ToLower()!="pziarek")
			//  return;
			CultureInfo culture=(CultureInfo)CultureInfo.CurrentCulture.Clone();
			culture.DateTimeFormat.ShortDatePattern="yyyy-MM-dd";
			culture.DateTimeFormat.LongTimePattern="HH:mm:ss";
			Thread.CurrentThread.CurrentCulture=culture;
			if(Request["add"]!=null)
			{
				dal.NonQuery("INSERT INTO Query(cat,des,sql,con,type,cred,usr)select '{0}','desc','select getdate()date','pon-wdsq10\\IN2012.master','T','','{1}'",Request["add"],usr);
				Response.Redirect(Request.CurrentExecutionFilePath+"?cat="+Request["add"]);
			}
			if(Request["id"]!=null)
			{
				DataTable qry=dal.Query("select id,sql,con,des,type,param,cred,color,grad,glimit,zlink from query(nolock)where id='{0}'and (usr='{1}' or share like '%{1}%' or share like '%*%')",Request["id"],usr);
				if(qry.Rows.Count!=1)
					throw new Exception("Only 1 row expected.");
				ColorCache colorCacheDef=new ColorCache(qry.Rows[0]["grad"].ToString());
				string con=qry.Rows[0]["con"].ToString();
				string cred=qry.Rows[0]["cred"].ToString();
				string color=qry.Rows[0]["color"].ToString();
				System.Collections.Generic.List<string> cinfo=new System.Collections.Generic.List<string>(con.Split('.'));
				if(cred.Contains("."))
					cinfo.AddRange(cred.Split('.'));
				string sql=qry.Rows[0]["sql"].ToString();
				string desc=qry.Rows[0]["des"].ToString();
				string type=qry.Rows[0]["type"].ToString();
				string glimit=qry.Rows[0]["glimit"].ToString();
				string zlink=qry.Rows[0]["zlink"].ToString();
				int limit=int.Parse(glimit);
				int sort;
				int.TryParse(qry.Rows[0]["param"].ToString(),out sort);
				Page.Title=desc;
				string constr=default(string);
				if(cinfo.Count==2)
					constr=string.Format("server={0};database={1};integrated security=sspi",cinfo.ToArray());
				else if(cinfo.Count==4)
					constr=string.Format("server={0};database={1};uid={2};pwd={3}",cinfo.ToArray());
				else
					throw new Exception("Connection info not sufficient");
				SQL data=new SQL(constr);
				DataTable srcTab=data.Query(sql);
				if(srcTab.Rows.Count==0)
				{
					form1.Controls.Add(new Elem("No Data!"));
					return;
				}
				if(type=="G")
				{

					if(limit==0)
						limit=20;
					DateReport repo=new DateReport(srcTab);
					string img=repo.Create(desc,limit);
					HtmlImage imag=new HtmlImage();
					imag.Src="http://localhost/img//"+img;
					form1.Controls.Add(imag);
					return;
				}


				GridView grData=null;
				if(type=="T")
					grData=Helper.Table(srcTab);
				else if(type=="P")
				{
					grData=Helper.Table(Helper.Transform(srcTab,sort));
					grData.Style.Add("font","11px tahoma");
					float max=0;
					if(!float.TryParse(qry.Rows[0]["glimit"].ToString(),out max)||max==0)
						max=float.Parse(srcTab.Compute("max("+srcTab.Columns[srcTab.Columns.Count-1].ColumnName+")",null).ToString());
					float dummy;
					foreach(GridViewRow row in grData.Rows)
					{
						if(color=="")
							for(int i=0;i<srcTab.Columns.Count-2;i++)
								row.Cells[i].BackColor=colorCacheDef.StrToColor(row.Cells[i].Text);
						for(int i=srcTab.Columns.Count-2;i<row.Cells.Count;i++)
							if(float.TryParse(row.Cells[i].Text,out dummy))
							{
								dummy=Math.Abs(dummy);
								row.Cells[i].BackColor=colorCacheDef.HeatMap(dummy,max);
								int pct=(int)(dummy*100/max);
								row.Cells[i].ToolTip=pct+"%";

								if(zlink!=""&&row.Cells[i].Text!="")
								{
									HtmlAnchor anc=new HtmlAnchor();

									List<string> qStr=new List<string>();
									for(int j=0;j<srcTab.Columns.Count-2;j++)
										qStr.Add(srcTab.Columns[j].ColumnName+"="+row.Cells[j].Text);
									qStr.Add(srcTab.Columns[srcTab.Columns.Count-2].ColumnName+"="+grData.HeaderRow.Cells[i].Text);
									anc.HRef=zlink+"?"+string.Join("&",qStr.ToArray());
									anc.InnerText=row.Cells[i].Text;
									row.Cells[i].Controls.Add(anc);
								}


							}
					}
				}
				foreach(string line in color.Split(new string[] { Environment.NewLine },StringSplitOptions.RemoveEmptyEntries))
				{
					if(line.Trim()==""||line.StartsWith("-"))
						continue;
					foreach(string str in line.Split(new string[] { ";" },StringSplitOptions.RemoveEmptyEntries))
					{
						ColorCache colorCache=colorCacheDef;
						if(str.Trim()==""||str.StartsWith("-"))
							continue;
						string[] colData=str.Trim().Split(new char[] { ':' },2);
						//if(colData[0].StartsWith("-"))
						//  continue;
						int column=int.Parse(colData[0])-1;
						string action=colData[1];
						//string[] actParts=action.Split(',');
						List<string> actParts=new List<string>();
						Match mf=Regex.Match(action,"(.+?)\\((.+?)\\)");
						if(mf.Success)
						{
							string f1=mf.Groups[1].Value;
							string[] f2=mf.Groups[2].Value.Split(new char[] { ',' },2);
							actParts.Add(f1);
							actParts.AddRange(f2);
						}
						else
						{
							actParts.AddRange(action.Split(','));
						}
						if(actParts[0]=="max")
						{
							float max=limit;
							if(actParts.Count==3)
							{
								max=float.Parse(actParts[1]);
								colorCache=new ColorCache(actParts[2]);

							}
							else if(actParts.Count==2)
								max=float.Parse(actParts[1]);
							if(max==0)
								foreach(GridViewRow row in grData.Rows)
								{
									float fl=0;
									if(float.TryParse(row.Cells[column].Text,out fl))
									{
										fl=Math.Abs(fl);
										if(fl>max)
											max=fl;
									}
								}
							foreach(GridViewRow row in grData.Rows)
							{
								float val=0;
								if(float.TryParse(row.Cells[column].Text,out val))
									row.Cells[column].BackColor=colorCache.HeatMap(Math.Abs(val),max);
							}
						}
						else
							foreach(GridViewRow row in grData.Rows)
							{
								if(actParts[0]=="sub")
								{
									int start=int.Parse(actParts[1]);
									int len=int.Parse(actParts[2]);
									if(row.Cells[column].Text.Length>=start+len)
										row.Cells[column].BackColor=colorCache.StrToColor(row.Cells[column].Text.Substring(start,len));
								}
								if(actParts[0]=="def")
									row.Cells[column].BackColor=colorCache.StrToColor(row.Cells[column].Text);
								if(actParts[0]=="cont")
									if(row.Cells[column].Text.ToLower().Contains(actParts[1].ToLower()))
									{
										Color hCol=default(Color);
										if(actParts.Count==3)
											//hCol=Color.FromName(actParts[2]);
											hCol=ColorCache.Convert(actParts[2]);
										else
											hCol=colorCache.StrToColor(actParts[1]+"rand");
										row.Cells[column].BackColor=hCol;
									}
								if(actParts[0]=="row")
									if(row.Cells[column].Text.ToLower().Contains(actParts[1].ToLower()))
									{
										Color hCol=default(Color);
										if(actParts.Count==3)
											hCol=ColorCache.Convert(actParts[2]);
										else
											hCol=colorCache.StrToColor(actParts[1]+"rand");
										row.BackColor=hCol;
									}
								if(actParts[0]=="high")
									if(row.Cells[column].Text.ToLower().Contains(actParts[1].ToLower()))
									{
										Color hCol=default(Color);
										if(actParts.Count==3)
											hCol=ColorCache.Convert(actParts[2]);
										else
											hCol=colorCache.StrToColor(actParts[1]+"rand");
										row.Cells[column].Text=Regex.Replace(row.Cells[column].Text,actParts[1],"<span style=\"background:"+ColorTranslator.ToHtml(hCol)+";\">"+actParts[1]+"</span>",RegexOptions.IgnoreCase);
									}
								if(actParts[0]=="link"&&row.Cells[column].Text!="&nbsp;")
								{
									HtmlAnchor anc=new HtmlAnchor();
									anc.HRef=string.Format(actParts[1],row.Cells[column].Text);
									anc.InnerText=row.Cells[column].Text;
									row.Cells[column].Controls.Add(anc);
								}
							}
					}
				}
				form1.Controls.Add(grData);
				return;
			}
			DataSet cats=dal.QuerySet("select cat,count(*)[cnt]from query(nolock)where usr='{0}' or share like '%{0}%' or share like '%*%' group by cat;select ISNULL(NULLIF(subcat,''),'NULL')sub,count(*)[cnt]from query(nolock)where usr='{0}' or share like '%{0}%' or share like '%*%' group by ISNULL(NULLIF(subcat,''),'NULL')",usr);
			ctrlMenu mnuCat=new ctrlMenu();
			mnuCat.Selected=Request["cat"];
			if(cats.Tables[0].Rows.Count==0)
				form1.Controls.Add(new Elem("<a href=\"?add=cat\" onclick=\"return confirm('New report?');\">start</a>",Request["cat"]));

			foreach(DataRow row in cats.Tables[0].Rows)
				mnuCat.Add(row["cat"],string.Format("{0}({1})",row["cat"],row["cnt"]),string.Format("?cat={0}",row["cat"]));
			form1.Controls.Add(mnuCat);
			if(Request["cat"]!=null)
				form1.Controls.Add(new Elem("<a href=\"?add={0}\" onclick=\"return confirm('New report?');\">add[{0}]</a>",Request["cat"]));
			if(Request["edit"]!=null)
				form1.Controls.Add(new Elem("<a href=\"?dup={0}\" onclick=\"return confirm('Duplicate report?');\">duplicate[{0}]</a>",Request["edit"]));

			form1.Controls.Add(new Elem("<hr/>"));
			//---------------------------

			ctrlMenu subCat=new ctrlMenu();
			subCat.Selected=Request["sub"];

			foreach(DataRow row in cats.Tables[1].Rows)
				subCat.Add(row["sub"],string.Format("{0}({1})",row["sub"],row["cnt"]),string.Format("?sub={0}",row["sub"]));
			form1.Controls.Add(subCat);
			form1.Controls.Add(new Elem("<hr/>"));
			//-------------------
			if(!IsPostBack&&(Request["cat"]!=null||Request["sub"]!=null))
			{
				ColorCache colorCache=new ColorCache();
				string catWhereKeyword="cat";
				if(Request["sub"]!=null)
					catWhereKeyword="subcat";
				string where="";
				if(catWhereKeyword=="subcat"&&Request["sub"]=="NULL")
					where="ISNULL(NULLIF(subcat,''),'NULL')='NULL'";
				else
					where=string.Format("{1}='{0}'",Request["cat"]??Request["sub"],catWhereKeyword);

				DataTable cat=dal.Query("select id,cat,subcat,des,type,'edit'edit,'csv'csv,usr owner,share from query(nolock)where {0} and (usr='{1}' or share like '%{1}%' or share like '%*%') order by subcat,id",where,usr);
				GridView gw=Helper.Table(cat);
				foreach(GridViewRow row in gw.Rows)
				{
					string id=row.Cells[0].Text;
					row.Cells[3].Text=string.Format("<a href=\"?id={0}\">{1}</a>",id,row.Cells[3].Text);
					row.Cells[5].Text=string.Format("<a href=\"?edit={0}\">{1}</a>",id,row.Cells[5].Text);
					row.Cells[1].BackColor=row.Cells[2].BackColor=colorCache.StrToColor(row.Cells[2].Text);
				}
				form1.Controls.Add(gw);
			}
			if(Request["dup"]!=null)
			{
				DataTable tab=dal.Query("insert into query(cat,des,sql,con,type,param,cred,color,subcat,grad,glimit,usr,share)select cat,des,sql,con,type,param,cred,color,subcat,grad,glimit,'{1}',share from query where id={0} and (usr='{1}' or share like '%{1}%' or share like '%*%') select @@identity",Request["dup"],usr);
				Response.Redirect(Page.AppRelativeVirtualPath+"?edit="+tab.Rows[0][0],true);
			}
			if(Request["edit"]!=null)
			{
				if(IsPostBack)
				{
					dal.NonQuery("update query set sql='{1}',des='{2}',cat='{3}',subcat='{4}',type='{5}',param='{6}',con='{7}',color='{8}',grad='{9}',glimit='{10}',keywords='{11}',share='{12}',zlink='{13}' where id={0} and usr='{14}'",Request["edit"],Request["sql"].Replace("'","''"),Request["desc"],Request["cat"],Request["subcat"],Request["type"],Request["param"],Request["con"],Request["color"],Request["grad"],Request["glimit"],Request["keywords"],Request["share"],Request["zlink"],usr);
					Response.Redirect(Page.AppRelativeVirtualPath+"?edit="+Request["edit"],true);
				}
				DataTable qry=dal.Query("select id,sql,con,des,type,param,cat,subcat,color,grad,glimit,keywords,usr,share,zlink from query(nolock)where id='{0}' and (usr='{1}' or share like '%{1}%' or share like '%*%')",Request["edit"],usr);
				if(qry.Rows.Count!=1)
					throw new Exception("Only 1 row expected.");
				string con=qry.Rows[0]["con"].ToString();
				string sql=qry.Rows[0]["sql"].ToString();
				string desc=qry.Rows[0]["des"].ToString();
				string type=qry.Rows[0]["type"].ToString();
				string cat=qry.Rows[0]["cat"].ToString();
				string subcat=qry.Rows[0]["subcat"].ToString();
				string param=qry.Rows[0]["param"].ToString();
				string color=qry.Rows[0]["color"].ToString();
				string grad=qry.Rows[0]["grad"].ToString();
				string glimit=qry.Rows[0]["glimit"].ToString();
				string keywords=qry.Rows[0]["keywords"].ToString();
				string share=qry.Rows[0]["share"].ToString();
				string usrFromDb=qry.Rows[0]["usr"].ToString();
				string zlink=qry.Rows[0]["zlink"].ToString();
				Page.Title=desc;
				if(string.Compare(usr,usrFromDb,true)==0)
					form1.Controls.Add(new Elem("<a href=\"javascript:document.forms[0].submit();\">Save</a>"));

				form1.Controls.Add(new Elem("<a href=\"?id={0}\">View</a><hr/>",Request["edit"]));
				form1.Controls.Add(new Elem("<table style=\"border-collapse:collapse;\">"));
				form1.Controls.Add(new Elem("<tr><td>Desc:<br/><input size=\"60\" type=\"text\" name=\"desc\" value=\"{0}\"/></td>",desc));
				form1.Controls.Add(new Elem("<td>Cat:<br/><input size=\"20\" type=\"text\" name=\"cat\" value=\"{0}\"/></td>",cat));
				form1.Controls.Add(new Elem("<td>Subcat:<br/><input size=\"20\" type=\"text\" name=\"subcat\" value=\"{0}\"/></td></tr>",subcat));
				form1.Controls.Add(new Elem("<tr><td colspan=\"3\">Keywords:<br/><input size=\"150\" type=\"text\" name=\"keywords\" value=\"{0}\"/></td></tr>",keywords));
				if(string.Compare(usr,usrFromDb,true)==0)
					form1.Controls.Add(new Elem("<tr><td colspan=\"3\">Share with(* = all):<br/><input size=\"150\" type=\"text\" name=\"share\" value=\"{0}\"/></td></tr>",share));
				form1.Controls.Add(new Elem("<tr><td colspan=\"3\">zColumn Link:<br/><input size=\"150\" type=\"text\" name=\"zlink\" value=\"{0}\"/></td></tr>",zlink));

				form1.Controls.Add(new Elem("<tr><td colspan=\"3\">Sql:<br/><textarea rows=\"12\" style=\"width:98%;\" name=\"sql\">{0}</textarea></td></tr>",sql));

				form1.Controls.Add(new Elem("<tr><td>Con(srv.db[.usr.pwd]):<br/><input size=\"60\" type=\"text\" name=\"con\" value=\"{0}\"/></td>",con));
				form1.Controls.Add(new Elem("<td>Type(P - pivot,T - table):<br/><input size=\"10\" type=\"text\" name=\"type\" value=\"{0}\"/></td>",type));
				form1.Controls.Add(new Elem("<td>Sort X(1:string, 2:int):<br/><input size=\"30\" type=\"text\" name=\"param\" value=\"{0}\"/></td></tr>",param));
				form1.Controls.Add(new Elem("<tr><td colspan=\"2\">Gradient override:<br/><input size=\"90\" type=\"text\" name=\"grad\" value=\"{0}\"/></td>",grad));
				form1.Controls.Add(new Elem("<td>Grad limit:<br/><input size=\"30\" type=\"text\" name=\"glimit\" value=\"{0}\"/></td></tr>",glimit));
				form1.Controls.Add(new Elem("<tr><td colspan=\"3\">Style(def,high,row,link,cont,sub):<br/><textarea rows=\"6\" style=\"width:98%;\" name=\"color\">{0}</textarea></td></tr>",color));
				form1.Controls.Add(new Elem("</table>"));
			}
		}
	}
}
